<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8" />
    
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
  <title>
    jdbc详解 |  Yolin
  </title>
  <meta name="generator" content="hexo-theme-yilia-plus">
  
  <link rel="shortcut icon" href="/favicon.png" />
  
  
<link rel="stylesheet" href="/css/style.css">

  
<script src="/js/pace.min.js"></script>


  

  

<link rel="alternate" href="/atom.xml" title="Yolin" type="application/atom+xml">
</head>

</html>

<body>
  <div id="app">
    <main class="content">
      <section class="outer">
  <article id="post-jdbc详解" class="article article-type-post" itemscope
  itemprop="blogPost" data-scroll-reveal>

  <div class="article-inner">
    
    <header class="article-header">
       
<h1 class="article-title sea-center" style="border-left:0" itemprop="name">
  jdbc详解
</h1>
  

    </header>
    

    
    <div class="article-meta">
      <a href="/2020/09/13/jdbc%E8%AF%A6%E8%A7%A3/" class="article-date">
  <time datetime="2020-09-13T14:45:02.000Z" itemprop="datePublished">2020-09-13</time>
</a>
      
  <div class="article-category">
    <a class="article-category-link" href="/categories/Java-Web/">Java Web</a>
  </div>

      
      
<div class="word_count">
    <span class="post-time">
        <span class="post-meta-item-icon">
            <i class="ri-quill-pen-line"></i>
            <span class="post-meta-item-text"> 字数统计:</span>
            <span class="post-count">1.6k字</span>
        </span>
    </span>

    <span class="post-time">
        &nbsp; | &nbsp;
        <span class="post-meta-item-icon">
            <i class="ri-book-open-line"></i>
            <span class="post-meta-item-text"> 阅读时长≈</span>
            <span class="post-count">6分钟</span>
        </span>
    </span>
</div>

      
    </div>
    

    
    
    <div class="tocbot"></div>





    

    <div class="article-entry" itemprop="articleBody">
      


      

      
      <h1 id="jdbc概念"><a href="#jdbc概念" class="headerlink" title="jdbc概念"></a>jdbc概念</h1><p><strong>jdbc</strong>（Java Database Connectivity），是一种用于执行SQL语句的Java API，也就是java操作数据库，其本质是官方定义的一套操作所有关系型数据库的接口。</p>
<p>常见的关系数据库有<a href="https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93/8999831?fr=aladdin" target="_blank" rel="noopener">Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL</a>等，而jdbc就是为这些关系型数据库提供统一访问，数据库厂商自己写的实现类，封装成数据库驱动jar包。</p>
<p>真正执行的代码是驱动jar包中的实现类，如下形式使用</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//Person接口、Worker类</span></span><br><span class="line"><span class="comment">//父类引用指向子类对象</span></span><br><span class="line"> Person p=<span class="keyword">new</span> Worker();</span><br><span class="line"> <span class="comment">//多态调用</span></span><br><span class="line"> p.eat();</span><br></pre></td></tr></table></figure>

<h1 id="jdbc快速入门"><a href="#jdbc快速入门" class="headerlink" title="jdbc快速入门"></a>jdbc快速入门</h1><p><a href="https://yolin121.gitee.io/2020/05/19/Java%E8%BF%9E%E6%8E%A5mysql-jdbc/" target="_blank" rel="noopener">jdbc快速入门</a></p>
<h3 id="步骤："><a href="#步骤：" class="headerlink" title="步骤："></a>步骤：</h3><ol>
<li>导入驱动jar包</li>
<li>注册驱动</li>
<li>获取数据库连接对象 Connection</li>
<li>定义sql</li>
<li>获取执行sql语句的对象 Statement</li>
<li>执行sql，接收返回的结果</li>
<li>处理结果</li>
<li>释放资源</li>
</ol>
<a id="more"></a>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-09_21-18-28.jpg" alt=""></p>
<h1 id="详解各个对象"><a href="#详解各个对象" class="headerlink" title="详解各个对象"></a>详解各个对象</h1><h2 id="1-DriverManager：驱动管理对象"><a href="#1-DriverManager：驱动管理对象" class="headerlink" title="1. DriverManager：驱动管理对象"></a>1. DriverManager：驱动管理对象</h2><p>功能</p>
<h4 id="①-注册驱动"><a href="#①-注册驱动" class="headerlink" title="① 注册驱动"></a>① 注册驱动</h4><p> 写代码时使用：<br> <figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//获取Driver类对象，即将类字节码文件加载进内存</span></span><br><span class="line">Class.forName(<span class="string">"com.mysql.jdbc.Driver"</span>);</span><br></pre></td></tr></table></figure></p>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-09_22-54-11.jpg" alt=""></p>
<p>查看源码发现了静态代码块</p>
<p><strong>注册驱动是为了告诉程序该使用哪一个数据库驱动jar</strong></p>
<p>注意：mysql5之后的驱动jar包可以省略注册驱动的步骤</p>
<h4 id="②-获取数据库连接"><a href="#②-获取数据库连接" class="headerlink" title="② 获取数据库连接"></a>② 获取数据库连接</h4><h5 id="方法："><a href="#方法：" class="headerlink" title="方法："></a>方法：</h5><table>
<thead>
<tr>
<th>修饰与类型</th>
<th>方法描述</th>
</tr>
</thead>
<tbody><tr>
<td>static Connection</td>
<td>getConnection(String url, String user, String password) <strong>尝试建立与给定数据库URL的连接</strong></td>
</tr>
</tbody></table>
<h5 id="参数："><a href="#参数：" class="headerlink" title="参数："></a>参数：</h5><ul>
<li>url : 指定连接的路径（<strong>jdbc:mysql://ip地址:端口号/数据库名称</strong>）</li>
<li>user : 用户名</li>
<li>password : 密码</li>
</ul>
<p>小知识：如果连接的是本机mysql服务器，且mysql服务默认端口是3306，则url可简写为：<strong>jdbc:mysql:///数据库名称</strong></p>
<h2 id="2-Connection-数据库连接对象"><a href="#2-Connection-数据库连接对象" class="headerlink" title="2. Connection:数据库连接对象"></a>2. Connection:数据库连接对象</h2><p> 功能</p>
<h4 id="①-获取sql对象"><a href="#①-获取sql对象" class="headerlink" title="① 获取sql对象"></a>① 获取sql对象</h4> <figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="function">statement <span class="title">createStatement</span><span class="params">()</span></span></span><br><span class="line"><span class="function">preparedStatement <span class="title">prepareStatement</span><span class="params">(String sql)</span></span></span><br></pre></td></tr></table></figure>

<h4 id="②-管理事务"><a href="#②-管理事务" class="headerlink" title="② 管理事务"></a>② 管理事务</h4><ul>
<li>开启事物<blockquote>
<p>setAutoCommit(boolean autoCommit) 调用该方法设置参数为false，即开启事物</p>
</blockquote>
</li>
<li>提交事物<blockquote>
<p>commit()</p>
</blockquote>
</li>
<li>回滚事物<blockquote>
<p>rollback()</p>
</blockquote>
</li>
</ul>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-10_22-50-09.jpg" alt=""></p>
<h2 id="3-Statement-执行sql的对象"><a href="#3-Statement-执行sql的对象" class="headerlink" title="3. Statement:执行sql的对象"></a>3. Statement:执行sql的对象</h2><h4 id="常用方法"><a href="#常用方法" class="headerlink" title="常用方法"></a>常用方法</h4><ul>
<li>① boolean <strong>execute</strong>(String sql): <strong>可以执行任意的sql</strong>（了解）</li>
<li>② int <strong>executeUpdate</strong>(String sql):<strong>执行DML（insert、update、delete）语句</strong>，返回值是影响的行数，可判断DML是否执行成功</li>
<li>③ Resultset <strong>executeQuery</strong>(String sql):<strong>执行DQL(select)语句</strong></li>
</ul>
<h4 id="练习"><a href="#练习" class="headerlink" title="练习"></a>练习</h4><p>给student数据库的admin表添加一条数据</p>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-12_12-54-34.jpg" alt=""></p>
<p>同样的方法来修改数据</p>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-12_13-41-06.jpg" alt=""></p>
<h2 id="4-ResultSet-结果集对象，封装查询结果"><a href="#4-ResultSet-结果集对象，封装查询结果" class="headerlink" title="4. ResultSet:结果集对象，封装查询结果"></a>4. ResultSet:结果集对象，封装查询结果</h2><p>使用步骤：</p>
<ol>
<li>游标向下移动一行</li>
<li>判断是否有数据</li>
<li>获取数据</li>
</ol>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">Class.forName(<span class="string">"com.mysql.jdbc.Driver"</span>);</span><br><span class="line">String sql=<span class="string">"select * from admin;"</span>;</span><br><span class="line">conn = DriverManager.getConnection(<span class="string">"jdbc:mysql:///student"</span>, <span class="string">"root"</span>, <span class="string">"123"</span>);</span><br><span class="line">stat = conn.createStatement();</span><br><span class="line">rs = stat.executeQuery(sql);</span><br><span class="line"><span class="comment">//获取数据</span></span><br><span class="line"><span class="keyword">while</span>(rs.next())&#123;</span><br><span class="line">    <span class="keyword">int</span> id =rs.getInt(<span class="string">"id"</span>);</span><br><span class="line">    String name = rs.getString(<span class="string">"name"</span>);</span><br><span class="line">    String password = rs.getString(<span class="string">"password"</span>);</span><br><span class="line">    System.out.println(id+<span class="string">'/'</span>+name+<span class="string">'/'</span>+password);</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h4 id="练习一"><a href="#练习一" class="headerlink" title="练习一"></a>练习一</h4><p><strong>查询stu表中的数据，将其封装为对象，然后打印</strong></p>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-12_20-46-56.jpg" alt=""></p>
<h5 id="使用jdbc工具类改进"><a href="#使用jdbc工具类改进" class="headerlink" title="使用jdbc工具类改进"></a>使用jdbc工具类改进</h5><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//jdbc工具类</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">jdbcUtils</span> </span>&#123;</span><br><span class="line">    <span class="comment">//作用域提升，只有静态的变量才能被静态代码块和静态方法访问</span></span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String url;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String user;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String password;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> String driver;</span><br><span class="line"></span><br><span class="line">    <span class="comment">//是用静态代码块读取文件</span></span><br><span class="line">    <span class="keyword">static</span>&#123;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="comment">//1.创建Properties集合类</span></span><br><span class="line">            <span class="comment">//获取src路径下文件的方式ClassLoader类加载器</span></span><br><span class="line">            ClassLoader cl= jdbcUtils<span class="class">.<span class="keyword">class</span>.<span class="title">getClassLoader</span>()</span>;</span><br><span class="line">            URL res=cl.getResource(<span class="string">"jdbc.properties"</span>);</span><br><span class="line">            String path=res.getPath();</span><br><span class="line">            <span class="comment">//2.加载文件</span></span><br><span class="line">            Properties pro=<span class="keyword">new</span> Properties();</span><br><span class="line">            pro.load(<span class="keyword">new</span> FileReader(path));</span><br><span class="line">            <span class="comment">//3.获取数据赋值</span></span><br><span class="line">            url=pro.getProperty(<span class="string">"url"</span>);</span><br><span class="line">            user=pro.getProperty(<span class="string">"user"</span>);</span><br><span class="line">            password=pro.getProperty(<span class="string">"password"</span>);</span><br><span class="line">            driver=pro.getProperty(<span class="string">"driver"</span>);</span><br><span class="line">            <span class="comment">//4.注册驱动</span></span><br><span class="line">            Class.forName(driver);</span><br><span class="line">        &#125; <span class="keyword">catch</span> (IOException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125; <span class="keyword">catch</span> (ClassNotFoundException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> Connection <span class="title">getConnection</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> DriverManager.getConnection(url,user,password);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">close</span><span class="params">(ResultSet rs,Statement stat, Connection conn)</span></span>&#123;</span><br><span class="line">        <span class="keyword">if</span>(rs!=<span class="keyword">null</span>)&#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                rs.close();</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">                e.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">if</span>(stat!=<span class="keyword">null</span>)&#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                stat.close();</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">                e.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">if</span>(conn!=<span class="keyword">null</span>)&#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                conn.close();</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">                e.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-12_23-02-50.jpg" alt=""></p>
<p>这样可以避免代码冗余</p>
<h4 id="练习二"><a href="#练习二" class="headerlink" title="练习二"></a>练习二</h4><p>通过连接数据库查询用户输入的账号密码是否正确</p>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-13_16-13-17.jpg" alt=""></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">jdbcDemo6</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> </span>&#123;</span><br><span class="line">        Scanner sc=<span class="keyword">new</span> Scanner(System.in);</span><br><span class="line">        System.out.println(<span class="string">"请输入用户名："</span>);</span><br><span class="line">        String username = sc.nextLine();</span><br><span class="line">        System.out.println(<span class="string">"请输入密码："</span>);</span><br><span class="line">        String password=sc.nextLine();</span><br><span class="line">        <span class="keyword">boolean</span> flag = <span class="keyword">new</span> jdbcDemo6().login(username, password);</span><br><span class="line">        <span class="keyword">if</span> (flag)&#123;</span><br><span class="line">            System.out.println(<span class="string">"登录成功"</span>);</span><br><span class="line">        &#125;<span class="keyword">else</span>&#123;</span><br><span class="line">            System.out.println(<span class="string">"账号或密码错误！"</span>);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">//登录方法</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">boolean</span> <span class="title">login</span><span class="params">(String username,String password)</span></span>&#123;</span><br><span class="line">        Connection conn =<span class="keyword">null</span>;</span><br><span class="line">        Statement stat =<span class="keyword">null</span>;</span><br><span class="line">        ResultSet rs =<span class="keyword">null</span>;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">if</span>(username==<span class="keyword">null</span>||password==<span class="keyword">null</span>)&#123;</span><br><span class="line">            <span class="keyword">return</span> <span class="keyword">false</span>;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//连接数据库判断是否登陆成功</span></span><br><span class="line">        <span class="comment">//1.获取连接</span></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            conn = jdbcUtils.getConnection();</span><br><span class="line">            <span class="comment">//2.定义sql</span></span><br><span class="line">            String sql=<span class="string">"select * from admin where name='"</span>+username+<span class="string">"' and password='"</span>+password+<span class="string">"';"</span>;</span><br><span class="line">            <span class="comment">//3.获取执行sql的对象</span></span><br><span class="line">            stat = conn.createStatement();</span><br><span class="line">            <span class="comment">//4.执行sql</span></span><br><span class="line">            rs = stat.executeQuery(sql);</span><br><span class="line">            <span class="comment">//判断,如果有下一行，则返回true</span></span><br><span class="line">            <span class="keyword">return</span> rs.next();</span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;<span class="keyword">finally</span> &#123;</span><br><span class="line">            jdbcUtils.close(rs,stat,conn);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">return</span> <span class="keyword">false</span>;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h2 id="5-PreparedStatement-执行sql的对象"><a href="#5-PreparedStatement-执行sql的对象" class="headerlink" title="5. PreparedStatement:执行sql的对象"></a>5. PreparedStatement:执行sql的对象</h2><p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-13_16-47-15.jpg" alt=""></p>
<p>上面的代码有个致命错误，按如图输入竟然会登陆成功<br>，这是由于<strong>SQL注入</strong>引起的，在拼接sql时有一些sql的特殊关键字参与字符串的拼接，而这样会造成安全性问题。</p>
<p>所以上面代码实际执行的sql是</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 返回true</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">admin</span> <span class="keyword">where</span> <span class="keyword">name</span>=<span class="string">'4545'</span> <span class="keyword">and</span> <span class="keyword">password</span>=<span class="string">'a'</span> <span class="keyword">or</span> <span class="string">'a'</span>=<span class="string">'a'</span></span><br></pre></td></tr></table></figure>

<p>为了解决sql注入问题：使用<strong>PreparedStatement对象</strong>来解决</p>
<p>该接口继承自Statement接口，表示<strong>预编译的SQL语句的对象</strong></p>
<p><strong>预编译的SQL</strong>： 参数使用 ? 作为占位符,于是将sql语句改为：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> <span class="keyword">admin</span> <span class="keyword">where</span> <span class="keyword">name</span>=? <span class="keyword">and</span> <span class="keyword">password</span>=?;</span><br></pre></td></tr></table></figure>

<p>接着获取执行sql语句的对象</p>
<blockquote>
<p>PreparedStatement  Connection.prepareStatement(String sql)</p>
</blockquote>
<p>给 ? 赋值：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">setXXX(参数<span class="number">1</span>，参数<span class="number">2</span>)</span><br><span class="line"><span class="comment">//参数1：？的位置编号从1开始</span></span><br><span class="line"><span class="comment">//参数2：？的值</span></span><br></pre></td></tr></table></figure>

<p>然后执行sql，接受返回结果，不需要传递sql语句</p>
<p>效果如图：<br><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-13_18-33-35.jpg" alt=""></p>
<h1 id="JDBC控制事务"><a href="#JDBC控制事务" class="headerlink" title="JDBC控制事务"></a>JDBC控制事务</h1><ol>
<li>事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败</li>
<li>操作：</li>
</ol>
<ul>
<li>开启事务</li>
<li>提交事务</li>
<li>回滚事务</li>
</ul>
<p>3.使用Connection对象来管理事务</p>
<ul>
<li>开启事物：setAutoCommit(boolean autoCommit):调用该方法设置参数为false，即开启事务（<strong>在执行sql之前开启事务</strong>）</li>
<li>提交事物：commit() (<strong>当所有sql都执行完提交事务</strong>)</li>
<li>回滚事务：rollback（） （<strong>在catch中回滚事务</strong>）</li>
</ul>
<p>来看一个例子：<br><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-13_22-21-38.jpg" alt=""></p>
<p>手动制造异常<br><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-13_22-34-59.jpg" alt=""></p>
<p>需使用控制事务来解决，一旦出现异常就会回滚到开启事务状态</p>
<p><img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/Snipaste_2020-09-13_22-54-56.jpg" alt=""></p>

      
      <!-- reward -->
      
    </div>
      <!-- copyright -->
      
    <footer class="article-footer">
      
      
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/jdbc/" rel="tag">jdbc</a></li></ul>


    </footer>

  </div>

  
  
  <nav class="article-nav">
    
      <a href="/2020/09/15/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0/" class="article-nav-link">
        <strong class="article-nav-caption">上一篇</strong>
        <div class="article-nav-title">
          
            数据库连接池
          
        </div>
      </a>
    
    
      <a href="/2020/09/09/SQL%E4%BB%8E%E5%88%A0%E5%BA%93%E5%88%B0%E8%B7%91%E8%B7%AF-%E4%BA%8C/" class="article-nav-link">
        <strong class="article-nav-caption">下一篇</strong>
        <div class="article-nav-title">SQL从删库到跑路(二)</div>
      </a>
    
  </nav>


  

  
  
<!-- valine评论 -->
<div id="vcomments-box">
    <div id="vcomments">
    </div>
</div>
<script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src='https://cdn.jsdelivr.net/npm/valine@1.3.10/dist/Valine.min.js'></script>
<script>
    new Valine({
        el: '#vcomments',
        notify: false,
        verify: '',
        app_id: '',
        app_key: '',
        path: window.location.pathname,
        avatar: 'mp',
        placeholder: '给我的文章加点评论吧~',
        recordIP: true
    });
    const infoEle = document.querySelector('#vcomments .info');
    if (infoEle && infoEle.childNodes && infoEle.childNodes.length > 0) {
        infoEle.childNodes.forEach(function (item) {
            item.parentNode.removeChild(item);
        });
    }
</script>
<style>
    #vcomments-box {
        padding: 5px 30px;
    }

    @media screen and (max-width: 800px) {
        #vcomments-box {
            padding: 5px 0px;
        }
    }

    #vcomments-box #vcomments {
        background-color: #fff;
    }

    .v .vlist .vcard .vh {
        padding-right: 20px;
    }

    .v .vlist .vcard {
        padding-left: 10px;
    }
</style>

  

  
  
  

</article>
</section>
      <footer class="footer">
  <div class="outer">
    <ul class="list-inline">
      <li>
        &copy;
        2020-2021
        张金
      </li>
      <li>
        
      </li>
    </ul>
    <ul class="list-inline">
      <li>
        
        
        <span>
  <i>PV:<span id="busuanzi_value_page_pv"></span></i>
  <i>UV:<span id="busuanzi_value_site_uv"></span></i>
</span>
        
      </li>
      <li>
        <!-- cnzz统计 -->
        
        <script type="text/javascript" src='https://s9.cnzz.com/z_stat.php?id=1278069914&amp;web_id=1278069914'></script>
        
        <img src="https://yolin121.oss-cn-beijing.aliyuncs.com/blogimg/beian.png">
        <a href="https://beian.miit.gov.cn"  style="color:#f72b07" target="_blank">陕ICP备20002449号</a>
      </li>

    </ul>
  </div>
</footer>
    <div class="to_top">
        <div class="totop" id="totop">
  <i class="ri-arrow-up-line"></i>
</div>
      </div>
    </main>
      <aside class="sidebar">
        <button class="navbar-toggle"></button>
<nav class="navbar">
  
  <div class="logo">
    <a href="/"><img src="/images/ayer-side.svg" alt="Yolin"></a>
  </div>
  
  <ul class="nav nav-main">
    
    <li class="nav-item">
      <a class="nav-item-link" href="/">主页</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/archives">归档</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/categories">分类</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/tags">标签</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/playlist">歌单</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/video">视频</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/about">关于我</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/friends">友情链接</a>
    </li>
    
  </ul>
</nav>
<nav class="navbar navbar-bottom">
  <ul class="nav">
    <li class="nav-item">
      
      <a class="nav-item-link nav-item-search"  title="Search">
        <i class="ri-search-line"></i>
      </a>
      
      
      <a class="nav-item-link" target="_blank" href="/atom.xml" title="RSS Feed">
        <i class="ri-rss-line"></i>
      </a>
      
    </li>
  </ul>
</nav>
<div class="search-form-wrap">
  <div class="local-search local-search-plugin">
  <input type="search" id="local-search-input" class="local-search-input" placeholder="Search...">
  <div id="local-search-result" class="local-search-result"></div>
</div>
</div>
      </aside>
      <div id="mask"></div>

<!-- #reward -->
<div id="reward">
  <span class="close"><i class="ri-close-line"></i></span>
  <p class="reward-p"><i class="ri-cup-line"></i>请我喝杯咖啡吧~</p>
  <div class="reward-box">
    
    <div class="reward-item">
      <img class="reward-img" src="/images/alipay.jpg">
      <span class="reward-type">支付宝</span>
    </div>
    
    
    <div class="reward-item">
      <img class="reward-img" src="/images/wechat.jpg">
      <span class="reward-type">微信</span>
    </div>
    
  </div>
</div>
      
<script src="/js/jquery-2.0.3.min.js"></script>


<script src="/js/jquery.justifiedGallery.min.js"></script>


<script src="/js/lazyload.min.js"></script>


<script src="/js/busuanzi-2.3.pure.min.js"></script>


<script src="/js/share.js"></script>




<script>
  try {
    var typed = new Typed("#subtitle", {
    strings: ['肠断月明红豆蔻,','月似当时,','人似当时否？'],
    startDelay: 1500,
    typeSpeed: 200,
    loop: true,
    backSpeed: 40,
    showCursor: true
    });
  } catch (err) {
  }
  
</script>




<script src="/js/tocbot.min.js"></script>

<script>
  // Tocbot_v4.7.0  http://tscanlin.github.io/tocbot/
  tocbot.init({
    tocSelector: '.tocbot',
    contentSelector: '.article-entry',
    headingSelector: 'h1, h2, h3, h4, h5, h6',
    hasInnerContainers: true,
    scrollSmooth: true,
    scrollContainer:'main',
    positionFixedSelector: '.tocbot',
    positionFixedClass: 'is-position-fixed',
    fixedSidebarOffset: 'auto',
    onClick: (e) => {
      $('.toc-link').removeClass('is-active-link');
      $(`a[href=${e.target.hash}]`).addClass('is-active-link');
      $(e.target.hash).scrollIntoView();
      return false;
    }
  });
</script>


<script>
  var ayerConfig = {
    mathjax: true
  }
</script>


<script src="/js/ayer.js"></script>


<script src="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.css">


<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">

    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>

    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">

        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>

        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">

            <div class="pswp__top-bar">

                <!--  Controls are self-explanatory. Order can be changed. -->

                <div class="pswp__counter"></div>

                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>

                <button class="pswp__button pswp__button--share" style="display:none" title="Share"></button>

                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>

                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>

                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                        <div class="pswp__preloader__cut">
                            <div class="pswp__preloader__donut"></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div>
            </div>

            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>

            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>

            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>

        </div>

    </div>

</div>

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/default-skin/default-skin.css">
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe-ui-default.min.js"></script>

<script>
    function viewer_init() {
        let pswpElement = document.querySelectorAll('.pswp')[0];
        let $imgArr = document.querySelectorAll(('.article-entry img:not(.reward-img)'))

        $imgArr.forEach(($em, i) => {
            $em.onclick = () => {
                // slider展开状态
                // todo: 这样不好，后面改成状态
                if (document.querySelector('.left-col.show')) return
                let items = []
                $imgArr.forEach(($em2, i2) => {
                    let img = $em2.getAttribute('data-idx', i2)
                    let src = $em2.getAttribute('data-target') || $em2.getAttribute('src')
                    let title = $em2.getAttribute('alt')
                    // 获得原图尺寸
                    const image = new Image()
                    image.src = src
                    items.push({
                        src: src,
                        w: image.width || $em2.width,
                        h: image.height || $em2.height,
                        title: title
                    })
                })
                var gallery = new PhotoSwipe(pswpElement, PhotoSwipeUI_Default, items, {
                    index: parseInt(i)
                });
                gallery.init()
            }
        })
    }
    viewer_init()
</script>



<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
      tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
  });

  MathJax.Hub.Queue(function() {
      var all = MathJax.Hub.getAllJax(), i;
      for(i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
      }
  });
</script>

<script src="https://cdn.jsdelivr.net/npm/mathjax@2.7.6/unpacked/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>


<script type="text/javascript" src="https://js.users.51.la/20544303.js"></script>
  </div>
</body>

</html>